﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using MySql.Data.Types;
using Pixysoft.Framework.XSchEmA;
using Pixysoft.Framework.XSchEmA.Entity;
using System.Data.SqlClient;
using Pixysoft.Framework.Verifications;

namespace Pixysoft.Framework.Noebe.Command
{
    /// <summary>
    /// : -> @
    /// </summary>
    class SqlServerCommandGenerator : ICommandGenerator
    {
        public string GetPagingSQL(string tableName, string pagingColumnName, string condition, int pageCapacity, int pageIndex, bool reverse)
        {
            StringBuilder builder = new StringBuilder();

            builder.AppendFormat("SELECT TOP {0} * FROM {1} WHERE", pageCapacity, tableName);

            if (!string.IsNullOrEmpty(condition))
            {
                builder.Append(" ").Append(condition).Append(" AND");
            }

            if (reverse)
                builder.AppendFormat(" ( {0} <= ( SELECT MIN({0}) FROM ( SELECT TOP {1} {0} FROM {2} ORDER BY {0} DESC ) AS T ) ) ORDER BY {0} DESC",
                    pagingColumnName, pageCapacity * pageIndex == 0 ? 1 : pageCapacity * pageIndex, tableName);
            else
                builder.AppendFormat(" ( {0} >= ( SELECT MAX({0}) FROM ( SELECT TOP {1} {0} FROM {2} ORDER BY {0} ) AS T ) ) ORDER BY {0}",
                    pagingColumnName, pageCapacity * pageIndex == 0 ? 1 : pageCapacity * pageIndex, tableName);

            return builder.ToString();
        }

        public string GetManualPagingSQL(string sql, string pagingColumnName, int pageCapacity, int pageIndex, bool reverse)
        {
            throw Exceptions.UnexpectedResultException("not support manual sql.");
        }

        public string RegularSQL(string sql)
        {
            if (string.IsNullOrEmpty(sql))
                return sql;

            return sql.Replace(":", "@");
        }

        public IDbConnection GetConnection(string connStr)
        {
            return new SqlConnection(connStr);
        }


        public IDataParameter GetParameter(string paraName)
        {
            SqlParameter para = new SqlParameter();
            para.ParameterName = paraName;
            para.SqlDbType = SqlDbType.VarChar;
            para.SourceColumn = paraName;
            return para;
        }

        public IDataParameter GetParameter(string paraName, DotNetDataType type)
        {
            SqlParameter para = new SqlParameter();
            para.ParameterName = paraName;
            para.SqlDbType = GetParaType(type);
            para.SourceColumn = paraName;
            return para;
        }

        public IDataParameter GetParameter(ColumnSchema column)
        {
            SqlParameter para = new SqlParameter(column.ColumnName,
                     GetParaType(column.DataType), 0, column.ColumnName);
            para.Direction = ParameterDirection.Input;
            para.IsNullable = column.IsNullable;
            para.SourceVersion = DataRowVersion.Current;
            return para;
        }


        /// <summary>
        /// 取得参数类型
        /// </summary>
        /// <param name="type">The type.</param>
        /// <returns></returns>
        private SqlDbType GetParaType(DotNetDataType type)
        {
            switch (type)
            {
                case DotNetDataType.Char:
                    return SqlDbType.Char;

                case DotNetDataType.Int32:
                    return SqlDbType.Int;

                case DotNetDataType.Int64:
                    return SqlDbType.BigInt;//09-10-28

                case DotNetDataType.Boolean:
                    return SqlDbType.Bit;

                case DotNetDataType.DateTime:
                    return SqlDbType.DateTime;

                case DotNetDataType.Decimal:
                    return SqlDbType.Decimal;

                case DotNetDataType.Single:
                case DotNetDataType.Double:
                    return SqlDbType.Real;

                case DotNetDataType.Byte:
                case DotNetDataType.Object:
                    return SqlDbType.Image;

                case DotNetDataType.String:
                case DotNetDataType.UNKNOWN:
                default:
                    return SqlDbType.VarChar;
            }
        }
        /// <summary>
        /// 取得参数类型
        /// </summary>
        /// <param name="type">The type.</param>
        /// <returns></returns>
        private SqlDbType GetParaType(DataTypeSchema type)
        {
            switch (Pixysoft.EnumCoder.ToEnum<SqlServerDataTypeCollection>(type.DataType))
            {
                case SqlServerDataTypeCollection.TEXT:
                    return SqlDbType.Text;
                case SqlServerDataTypeCollection.NTEXT:
                    return SqlDbType.NText;

                case SqlServerDataTypeCollection.IMAGE:
                case SqlServerDataTypeCollection.SQL_VARIANT:
                    return SqlDbType.Image;


                case SqlServerDataTypeCollection.BIGINT:
                    return SqlDbType.BigInt;
                case SqlServerDataTypeCollection.UNIQUEIDENTIFIER:
                    return SqlDbType.UniqueIdentifier;
                case SqlServerDataTypeCollection.DATETIME:
                    return SqlDbType.DateTime;
                case SqlServerDataTypeCollection.SMALLDATETIME:
                    return SqlDbType.SmallDateTime;
                case SqlServerDataTypeCollection.TIMESTAMP:
                    return SqlDbType.Timestamp;
                case SqlServerDataTypeCollection.REAL:
                    return SqlDbType.Real;
                case SqlServerDataTypeCollection.FLOAT:
                    return SqlDbType.Float;
                case SqlServerDataTypeCollection.MONEY:
                    return SqlDbType.Money;
                case SqlServerDataTypeCollection.SMALLMONEY:
                    return SqlDbType.SmallMoney;
                case SqlServerDataTypeCollection.INT:
                    return SqlDbType.Int;
                case SqlServerDataTypeCollection.SMALLINT:
                    return SqlDbType.SmallInt;
                case SqlServerDataTypeCollection.TINYINT:
                    return SqlDbType.TinyInt;
                case SqlServerDataTypeCollection.BIT:
                    return SqlDbType.Bit;



                case SqlServerDataTypeCollection.NUMERIC:
                case SqlServerDataTypeCollection.DECIMAL:
                    return SqlDbType.Decimal;


                case SqlServerDataTypeCollection.CHAR:
                    return SqlDbType.Char;
                case SqlServerDataTypeCollection.VARCHAR:
                    return SqlDbType.VarChar;
                case SqlServerDataTypeCollection.NCHAR:
                    return SqlDbType.NChar;
                case SqlServerDataTypeCollection.NVARCHAR:
                    return SqlDbType.NVarChar;
                case SqlServerDataTypeCollection.BINARY:
                    return SqlDbType.Binary;
                case SqlServerDataTypeCollection.VARBINARY:
                    return SqlDbType.VarBinary;

                default:
                    return SqlDbType.VarChar;
            }
        }
    }
}
